﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Xml;
using System.Data.OleDb;
using SIIE_ERP.BaseDatos;

namespace SIIE_ERP.Codigo
{
    public class SrvDB
    {
        private static OleDbConnection _myConnection;

        public static OleDbConnection myConnection
        {
            get
            {
                if (_myConnection == null || _myConnection.State != ConnectionState.Open)
                {
                    try
                    {
                        _myConnection = new OleDbConnection(GetDefaultConnection());
                        _myConnection.Open();
                        _myConnection.StateChange += (s, e) =>
                        {
                            if (e.CurrentState == ConnectionState.Closed)
                            {
                                (s as OleDbConnection).Dispose();
                            }
                        };
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
                return _myConnection;
            }
        }

        public static void CloseConnection()
        {
            _myConnection.Close();
            _myConnection = null;
        }

        public static bool SetDatabasePath(string path, string file)
        {
            try
            {
                XmlDocument config = new XmlDocument();
                config.Load(file);
                XmlNode node = config.DocumentElement.SelectSingleNode("path");

                node.InnerText = path;
                XmlWriter writer = XmlWriter.Create(file);

                config.Save(writer);
                writer.Close();

                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        public static string GetDefaultConnection()
        {
            string connectionString;
            try
            {
                //SetDatabasePath("lalala", "config.xml");
                XmlDocument configurationFile = new XmlDocument();
               
             //   configurationFile.Load("config.xml");
                configurationFile.Load(@"C:\SIIE_Root\config.xml");
                XmlNode node = configurationFile.DocumentElement.SelectSingleNode("path");
                //connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source =" + node.InnerText + @"; Jet OLEDB:Database Password=adolfojavier";
                connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source =" + node.InnerText + @"; Persist Security Info=False;";
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return connectionString;
        }


        public static DataTable ExecuteQuery_Get(OleDbCommand command)
        {
            command.Connection = myConnection;
            try
            {
                OleDbDataAdapter da = new OleDbDataAdapter(command);
                DataSet dset = new DataSet();
                da.Fill(dset);
                return dset.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static Respuesta ExecuteQuery_Insert(OleDbCommand command)
        {
            command.Connection = myConnection;
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return new Respuesta(2, ex.Message);
            }
            return new Respuesta(1, "Insert Exitoso!");
        }

        public static Respuesta ExecuteQuery_Update(OleDbCommand command)
        {
            command.Connection = myConnection;
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return new Respuesta(2, ex.Message);
            }
            return new Respuesta(1, "Update Exitoso!");
        }

        public static Respuesta ExecuteQuery_Delete(OleDbCommand command)
        {
            command.Connection = myConnection;
            try
            {
                OleDbDataAdapter da = new OleDbDataAdapter(command);
                DataSet dset = new DataSet();
                da.Fill(dset);
            }
            catch (Exception ex)
            {
                return new Respuesta(2, ex.Message);
            }
            return new Respuesta(1, "Delete Exitoso!");
        }


        public static DataTable ExecuteQuery_Get(string query)
        {
            OleDbCommand command = new OleDbCommand(query, myConnection);
            try
            {
                OleDbDataAdapter da = new OleDbDataAdapter(command);
                DataSet dset = new DataSet();
                da.Fill(dset);
                return dset.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;

            }
        }

        public static Respuesta ExecuteQuery_Insert(string query)
        {
            OleDbCommand command = new OleDbCommand(query, myConnection);
            try
            {
                OleDbDataAdapter da = new OleDbDataAdapter(command);
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return new Respuesta(2, ex.Message);
            }
            return new Respuesta(1, "Insert Exitoso!");
        }

        public static Respuesta ExecuteQuery_Update(string query)
        {
            OleDbCommand command = new OleDbCommand(query, myConnection);
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return new Respuesta(2, ex.Message);
            }
            return new Respuesta(1, "Update Exitoso!");
        }

        public static Respuesta ExecuteQuery_Delete(string query)
        {
            OleDbCommand command = new OleDbCommand(query, myConnection);
            try
            {
                OleDbDataAdapter da = new OleDbDataAdapter(command);
                DataSet dset = new DataSet();
                da.Fill(dset);
            }
            catch (Exception ex)
            {
                return new Respuesta(2, ex.Message);
            }

            return new Respuesta(1, "Delete Exitoso!");
        }
    }
}